{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Java ODBCデータソースを登録\n",
    "\n",
    "* ODBC(Open Database Connectivity)\n",
    "    * リレーショナルデータベース管理システム (RDBMS) にアクセスするための共通インタフェース (API)\n",
    "    * マイクロソフトによって提唱された\n",
    "* JDBC(Java Database Connectivity)\n",
    "    * Javaとリレーショナルデータベースの接続のためのAPI\n",
    "* Javaで利用できるJDBCドライバ\n",
    "    * Type1 JDBC-ODBC ブリッジドライバ\n",
    "        * JDBCからの要求をODBCを経由してデータベースへアクセスする\n",
    "        * 先にODBCデータソースを登録する必要がある\n",
    "        * <font color=\"red\">Java8では標準から削除された</font>\n",
    "            * 今日では多くのデータベースがJDBCドライバを出しており、ODBCを経由させる必要性はほとんどなくなったため\n",
    "    * Type2 Native ブリッジドライバ\n",
    "    * Type3 ネットプロトコルドライバ\n",
    "    * Type4 ネイティブプロトコルドライバ\n",
    "        * PostgreSQL JDBC driverはこれ"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## PostgreSQLのドライバのインストール\n",
    "\n",
    "[PostgreSQL JDBC driver](https://jdbc.postgresql.org/download.html) よりjarファイルをダウンロード"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "--2017-05-31 03:01:10--  https://jdbc.postgresql.org/download/postgresql-42.1.1.jar\n",
      "Resolving jdbc.postgresql.org (jdbc.postgresql.org)... 174.143.35.228, 2001:4800:1501:1::228\n",
      "Connecting to jdbc.postgresql.org (jdbc.postgresql.org)|174.143.35.228|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 712452 (696K) [application/java-archive]\n",
      "Saving to: 'postgresql-42.1.1.jar'\n",
      "\n",
      "     0K .......... .......... .......... .......... ..........  7%  157K 4s\n",
      "    50K .......... .......... .......... .......... .......... 14%  312K 3s\n",
      "   100K .......... .......... .......... .......... .......... 21% 11.6M 2s\n",
      "   150K .......... .......... .......... .......... .......... 28%  330K 2s\n",
      "   200K .......... .......... .......... .......... .......... 35% 5.91M 1s\n",
      "   250K .......... .......... .......... .......... .......... 43%  114M 1s\n",
      "   300K .......... .......... .......... .......... .......... 50% 11.6M 1s\n",
      "   350K .......... .......... .......... .......... .......... 57% 11.6M 0s\n",
      "   400K .......... .......... .......... .......... .......... 64%  343K 0s\n",
      "   450K .......... .......... .......... .......... .......... 71% 9.92M 0s\n",
      "   500K .......... .......... .......... .......... .......... 79% 11.6M 0s\n",
      "   550K .......... .......... .......... .......... .......... 86%  298K 0s\n",
      "   600K .......... .......... .......... .......... .......... 93%  249M 0s\n",
      "   650K .......... .......... .......... .......... .....     100% 8.04M=1.0s\n",
      "\n",
      "2017-05-31 03:01:12 (709 KB/s) - 'postgresql-42.1.1.jar' saved [712452/712452]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### jarファイルをダウンロード\n",
    "#wget https://jdbc.postgresql.org/download/postgresql-42.1.1.jar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## DBに接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------\n",
      "--- Rows:1\n",
      "---------------------------------------------------\n",
      "usename:postgres\n",
      "usesysid:10\n",
      "usecreatedb:t\n",
      "usesuper:t\n",
      "usecatupd:t\n",
      "userepl:t\n",
      "passwd:md5d2743fc4ae70b03845da881b3d77b963\n",
      "valuntil:null\n",
      "useconfig:null\n",
      "---------------------------------------------------\n",
      "--- Rows:2\n",
      "---------------------------------------------------\n",
      "usename:prefuser\n",
      "usesysid:16384\n",
      "usecreatedb:f\n",
      "usesuper:f\n",
      "usecatupd:f\n",
      "userepl:f\n",
      "passwd:md548308ce61c5076c4d0d2c01676aeb78e\n",
      "valuntil:null\n",
      "useconfig:null\n",
      "---------------------------------------------------\n",
      "--- Rows:3\n",
      "---------------------------------------------------\n",
      "usename:jsd\n",
      "usesysid:16388\n",
      "usecreatedb:f\n",
      "usesuper:f\n",
      "usecatupd:f\n",
      "userepl:f\n",
      "passwd:md5075cef071f4d294944089ee00870501d\n",
      "valuntil:null\n",
      "useconfig:null\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PostgresConnect\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続。pg_shadowを表示するため管理者(postgres)\n",
    "      connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_test\", \"postgres\", \"pass\");\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      resultSet = statement.executeQuery(\"SELECT * FROM pg_shadow\");\n",
    "\n",
    "      //# 値の取得。resultSetから値を取り出している。fieldsに取り出した値を1行ずつ追加している\n",
    "      List<String> fields = new ArrayList<String>();\n",
    "      ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "      for (int i = 1; i <= rsmd.getColumnCount(); i++) {\n",
    "        fields.add(rsmd.getColumnName(i));\n",
    "      }\n",
    "\n",
    "      //# 結果の出力\n",
    "      int rowCount = 0;\n",
    "      while (resultSet.next()) {\n",
    "        rowCount++;\n",
    "\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "        System.out.println(\"--- Rows:\" + rowCount);\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "\n",
    "        //# 値は、「resultSet.getString(<フィールド名>)」で取得する。\n",
    "        for (String field : fields) {\n",
    "          System.out.println(field + \":\" + resultSet.getString(field));\n",
    "        }\n",
    "      }\n",
    "            \n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### メモ\n",
    "\n",
    "* createStatement()\n",
    "    * SQL文をデータベースに送るためのStatementオブジェクトを生成\n",
    "    * Statementオブジェクト\n",
    "        * 静的SQL文を実行し、作成された結果を返すために使用されるオブジェクト\n",
    "            * デフォルトでは、Statementオブジェクトごとに、同時に開くことができるResultSetオブジェクトは1つだけ\n",
    "* executeQuery(String sql)\n",
    "    * 指定されたSQL文を実行する\n",
    "    * 単一のResultSetオブジェクト(表のようなもの)を返す\n",
    "    * ResultSet\n",
    "        * データベースの結果セットを表すデータの表\n",
    "        * 通常データベースに照会する文を実行することによって生成される\n",
    "        * ResultSetオブジェクト\n",
    "            * カーソルがデータの現在の行を指し示すよう維持\n",
    "            * 初期状態では、カーソルは最初の行の先頭に配置"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
